﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Jalal.Library.Data;
using System.Data.Common;
using Jalal.Library.Utility;
using System.Data.SqlClient;
using Jalal.Library.Web;
using System.Configuration;

namespace BCGC.DAL
{
    public class UserData
    {
        public static DataTable GetAdminUserByLoginInfo(string username, string password)
        {
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_GetAdminUserByLoginInfo";
            dataMiner.AddParameter(command, "@SiteUserName", DbType.String, 50, ParameterDirection.Input, username);
            dataMiner.AddParameter(command, "@SiteUserPassword", DbType.String, 50, ParameterDirection.Input, password);

            return dataMiner.GetListData(command);
        }

        public static DataTable GetSiteUserByLoginInfo(string username, string password)
        {
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_GetUserByLoginInfo";
            dataMiner.AddParameter(command, "@SiteUserName", DbType.String, 50, ParameterDirection.Input, username);
            dataMiner.AddParameter(command, "@SiteUserPassword", DbType.String, 50, ParameterDirection.Input, password);

            return dataMiner.GetListData(command);
        }

        public static int CreateNewAccount(string username, string password, string email, 
            string secretQuestion, string secretAnswer, string verificationCode, int roleId)
        {
            object id = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_CreateNewUserAccount";
            dataMiner.AddParameter(command, "@Username", DbType.String, 50, ParameterDirection.Input, username);
            dataMiner.AddParameter(command, "@Password", DbType.String, 50, ParameterDirection.Input, password);
            dataMiner.AddParameter(command, "@Email", DbType.String, 50, ParameterDirection.Input, email);
            dataMiner.AddParameter(command, "@SecretQuestion", DbType.String, 500, ParameterDirection.Input, secretQuestion);
            dataMiner.AddParameter(command, "@SecretAnswer", DbType.String, 500, ParameterDirection.Input, secretAnswer);
            dataMiner.AddParameter(command, "@VerificationCode", DbType.String, 500, ParameterDirection.Input, verificationCode);
            dataMiner.AddParameter(command, "@RoleId", DbType.Int32, 0, ParameterDirection.Input, roleId);
            dataMiner.AddParameter(command, "ReturnValue", DbType.Int32, 0, ParameterDirection.ReturnValue, null);
            dataMiner.Execute(command, out id);

            return (int)CommonUtility.FilterNull(id, typeof(int));
        }

        public static int GetDefaultRoleId()
        {
            object roleId = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_GetDefaultRoleId";
            roleId = dataMiner.GetOneData(command);

            return (int)CommonUtility.FilterNull(roleId, typeof(int));
        }

        public static DataTable GetAllUsers(int currentPageIndex, string sortOrder, int pageSize, 
            string searchUsername, int? searchRoleId, DateTime? searchRegDateFrom,
            DateTime? searchRegDateTo, bool? searchActive, bool? searchLocked, out int totalData)
        {
            totalData = 0;
            object count = null;

            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();

            dataMiner.AddParameter(command, "@CurrentPageIndex", DbType.Int32, 0, ParameterDirection.Input, currentPageIndex);
            dataMiner.AddParameter(command, "@SortOrder", DbType.String, 500, ParameterDirection.Input, sortOrder);
            dataMiner.AddParameter(command, "@PageSize", DbType.Int32, 0, ParameterDirection.Input, pageSize);
            dataMiner.AddParameter(command, "@SearchUsername", DbType.String, 50, ParameterDirection.Input, searchUsername);
            dataMiner.AddParameter(command, "@SearchRoleId", DbType.Int32, 0, ParameterDirection.Input, searchRoleId);
            dataMiner.AddParameter(command, "@SearchRegDateFrom", DbType.DateTime, 0, ParameterDirection.Input, searchRegDateFrom);
            dataMiner.AddParameter(command, "@SearchRegDateTo", DbType.DateTime, 0, ParameterDirection.Input, searchRegDateTo);
            dataMiner.AddParameter(command, "@SearchActive", DbType.Boolean, 0, ParameterDirection.Input, searchActive);
            dataMiner.AddParameter(command, "@SearchLocked", DbType.Boolean, 0, ParameterDirection.Input, searchLocked);

            command.CommandText = "CSP_GetTotalUserCount";
            count = dataMiner.GetOneData(command);
            totalData = (int)CommonUtility.FilterNull(count, typeof(int));

            command.CommandText = "CSP_GetUserList";
            return dataMiner.GetListData(command);
        }

        public static DataTable GetAllRoles()
        {
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_GetRoleList";

            return dataMiner.GetListData(command);
        }

        public static int DeleteUsers(List<int> usersToDelete)
        {
            object count = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_DeleteUsers";
            dataMiner.AddParameter(command, "@Ids", DbType.String, -1, ParameterDirection.Input, DataMiner.FormatXMLForIdArray(usersToDelete));
            dataMiner.AddParameter(command, "ReturnValue", DbType.Int32, 0, ParameterDirection.ReturnValue, null);
            dataMiner.Execute(command, out count);

            return (int)CommonUtility.FilterNull(count, typeof(int));
        }

        public static int UpdateUserLocks(List<int> usersToLock, List<int> usersToUnlock)
        {
            object count = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_UpdateUserLock";
            dataMiner.AddParameter(command, "@UserIdsToLock", DbType.String, -1, ParameterDirection.Input, DataMiner.FormatXMLForIdArray(usersToLock));
            dataMiner.AddParameter(command, "@UserIdsToUnlock", DbType.String, -1, ParameterDirection.Input, DataMiner.FormatXMLForIdArray(usersToUnlock));
            dataMiner.AddParameter(command, "ReturnValue", DbType.Int32, 0, ParameterDirection.ReturnValue, null);
            dataMiner.Execute(command, out count);

            return (int)CommonUtility.FilterNull(count, typeof(int));
        }

        public static int GetUserCountByUsername(string username)
        {
            object count = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_GetUsernameCount";
            dataMiner.AddParameter(command, "@SiteUserName", DbType.String, 50, ParameterDirection.Input, username);
            count = dataMiner.GetOneData(command);

            return (int)CommonUtility.FilterNull(count, typeof(int));
        }

        public static int ActivateAccount(string verificationCode)
        {
            object count = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_ActivateAccountWithCode";
            dataMiner.AddParameter(command, "@VerificationCode", DbType.String, 500, ParameterDirection.Input, verificationCode);
            dataMiner.AddParameter(command, "ReturnValue", DbType.Int32, 0, ParameterDirection.ReturnValue, null);
            dataMiner.Execute(command, out count);

            return (int)CommonUtility.FilterNull(count, typeof(int));
        }

        public static DataTable GetSiteUserByUserId(int id)
        {
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_GetUserById";
            dataMiner.AddParameter(command, "@Id", DbType.Int32, 0, ParameterDirection.Input, id);
            
            return dataMiner.GetListData(command);
        }

        public static string UpdateVerificationCode(string username, string verificationCode)
        {
            object email = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_UpdateVerificationCodeByUsername";
            dataMiner.AddParameter(command, "@SiteUserName", DbType.String, 500, ParameterDirection.Input, username);
            dataMiner.AddParameter(command, "@VerificationCode", DbType.String, 500, ParameterDirection.Input, verificationCode);
            email = dataMiner.GetOneData(command);

            return (string)email;
        }

        public static DataTable GetUserInfoByUserId(int userID)
        {
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_GetUserInfoByUserId";
            dataMiner.AddParameter(command, "@UserID", DbType.Int32, 0, ParameterDirection.Input, userID);

            return dataMiner.GetListData(command);
        }

        public static int UpdateUserAccount(int id, string password, string email, 
            string secretQuestion, string secretAnswer)
        {
            object count = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_UpdateUserAccount";
            dataMiner.AddParameter(command, "@UserId", DbType.Int32, 0, ParameterDirection.Input, id);
            dataMiner.AddParameter(command, "@Password", DbType.String, 50, ParameterDirection.Input, password);
            dataMiner.AddParameter(command, "@Email", DbType.String, 50, ParameterDirection.Input, email);
            dataMiner.AddParameter(command, "@SecretQuestion", DbType.String, 500, ParameterDirection.Input, secretQuestion);
            dataMiner.AddParameter(command, "@SecretAnswer", DbType.String, 500, ParameterDirection.Input, secretAnswer);
            dataMiner.AddParameter(command, "ReturnValue", DbType.Int32, 0, ParameterDirection.ReturnValue, null);
            dataMiner.Execute(command, out count);

            return (int)CommonUtility.FilterNull(count, typeof(int));
        }

        public static int UpdateUserProfile(int id, string city, DateTime dateOfBirth,
            string element, string facebookProfileUrl, string favoriteGames, string messenger, 
            string mood, string nickName, string playerStyle, string realName, 
            bool useProfileImage, string userBio)
        {
            object count = null;
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_UpdateUserProfile";
            dataMiner.AddParameter(command, "@ProfileId", DbType.Int32, 0, ParameterDirection.Input, id);
            dataMiner.AddParameter(command, "@City", DbType.String, 50, ParameterDirection.Input, city);
            dataMiner.AddParameter(command, "@DateOfBirth", DbType.DateTime, 0, ParameterDirection.Input, dateOfBirth);
            dataMiner.AddParameter(command, "@Element", DbType.String, 50, ParameterDirection.Input, element);
            dataMiner.AddParameter(command, "@FacebookProfileUrl", DbType.String, 500, ParameterDirection.Input, facebookProfileUrl);
            dataMiner.AddParameter(command, "@FavoriteGames", DbType.String, 500, ParameterDirection.Input, favoriteGames);
            dataMiner.AddParameter(command, "@Messenger", DbType.String, 50, ParameterDirection.Input, messenger);
            dataMiner.AddParameter(command, "@Mood", DbType.String, 50, ParameterDirection.Input, mood);
            dataMiner.AddParameter(command, "@NickName", DbType.String, 500, ParameterDirection.Input, nickName);
            dataMiner.AddParameter(command, "@PlayerStyle", DbType.String, 50, ParameterDirection.Input, playerStyle);
            dataMiner.AddParameter(command, "@RealName", DbType.String, 500, ParameterDirection.Input, realName);
            dataMiner.AddParameter(command, "@UseProfileImage", DbType.Boolean, 0, ParameterDirection.Input, useProfileImage);
            dataMiner.AddParameter(command, "@UserBio", DbType.String, 1000, ParameterDirection.Input, userBio);
            dataMiner.AddParameter(command, "ReturnValue", DbType.Int32, 0, ParameterDirection.ReturnValue, null);
            dataMiner.Execute(command, out count);

            return (int)CommonUtility.FilterNull(count, typeof(int));
        }

        public static DataTable GetUserInfoByUserName(string userName)
        {
            DataMiner dataMiner = DataFactory.GetDataMiner();
            DbCommand command = DataFactory.GetCommand();
            command.CommandText = "CSP_GetUserInfoByUserName";
            dataMiner.AddParameter(command, "@UserName", DbType.String, 0, ParameterDirection.Input, userName);

            return dataMiner.GetListData(command);
        }

        public static DataTable GetFriendsByLikeName(string searchString, int userID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "CSP_GetFriendsByLikeName";
            command.CommandType = CommandType.StoredProcedure;

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[SiteConfigSection.SiteConfiguration.ConnectionStringName].ConnectionString);
            conn.Open();
            command.Connection = conn;
            command.Parameters.Add("@UserName", SqlDbType.NVarChar,0).Value="%"+searchString+"%";
            command.Parameters.Add("@UserID", SqlDbType.Int,0).Value=userID;

            DataTable result = new DataTable();
            DbDataReader reader = command.ExecuteReader();
            result.Load(reader);
            conn.Close();
            return result;
        }

       
    }
}
